const { db, sqlExec } = require('../db/mysql57');

const { formatDate } = require('../util/tool/date');

const bookDao = {
    /*
     * 专注打卡接口
     */
    async focuscard(uid, time, duration) {
        // 先查询昨天是否有打卡
        let consecutiveDays = 1;
        let sql = `select
                consecutiveDays
            from FocusCard
            where uid = ? and DATE(time) = ?`;
        let yestday = new Date(time);
        yestday.setDate(yestday.getDate() - 1);
        yestday = formatDate(yestday);
        let data = [uid, yestday];
        try {
            let res = await sqlExec(sql, data);
            if (res.length > 0) {
                consecutiveDays = res[0].consecutiveDays + 1;
            }
        } catch (err) {
            throw "数据库异常";
        }
        // 再查询这次打卡是否合理
        sql = `select
                DATE_FORMAT(MAX(time), '%Y-%m-%d %H:%i:%s') as time
            from FocusCard
            where uid = ?`;
        data = [uid];
        let res;
        try {
            res = await sqlExec(sql, data);
        } catch (err) {
            throw "数据库异常";
        }
        try {
            if (res.length > 0) {
                let lastTime = res[0].time;
                if (lastTime) {
                    let lastDate = new Date(lastTime);
                    let nowDate = new Date(time);
                    // 这里的逻辑为 两个时间戳相减 小于 duration 则直接返回非法打卡
                    let timeDifference = nowDate.getTime() - lastDate.getTime();
                    let durationInMilliseconds = duration * 60 * 1000;
                    if (timeDifference < durationInMilliseconds) {
                        throw "非法打卡，两次打卡时间间隔过小";
                    }
                }
            }
        } catch (err) {
            throw err;
        }
        // 插入新的打卡记录
        sql = `INSERT INTO FocusCard (uid, time, duration, consecutiveDays) VALUES (?, ?, ?, ?)`;
        data = [uid, time, duration, consecutiveDays];
        return sqlExec(sql, data);
    },
    async checkinStatistics(uid) {
        let sql = `select (
                select SUM(duration) from FocusCard where uid = ?
            ) as all_duration, (
                select COUNT(DISTINCT DATE(time)) from FocusCard where uid = ? 
            ) as total_days, (
                select consecutiveDays from FocusCard where uid = ? and id = (
                    select id from FocusCard where time = (
                        SELECT MAX(time) FROM FocusCard WHERE uid = ? LIMIT 1
                    ) LIMIT 1
                ) LIMIT 1
            ) as consecutiveDays`;
        let data = [ uid, uid, uid, uid ];
        return sqlExec(sql, data);
    },
    async checkinRanking() {
        /*
         * 排行榜接口
         * 返回：前100的名次
         */
        let sql = `SELECT
                f1.uid as uid,
                SUM(f1.duration) AS total_duration,
                COALESCE(
                    (
                        SELECT f2.consecutiveDays
                        FROM FocusCard AS f2
                        WHERE f2.uid = f1.uid AND DATE(f2.time) = CURDATE()
                        LIMIT 1
                    ),
                    (
                        SELECT f3.consecutiveDays
                        FROM FocusCard AS f3
                        WHERE f3.uid = f1.uid AND DATE(f3.time) = CURDATE() - INTERVAL 1 DAY
                        LIMIT 1
                    )
                ) as consecutiveDays,
                u.username as username,
                u.userIconId as userIconId
            FROM FocusCard as f1
            left join User as u on u.id = f1.uid
            GROUP BY f1.uid
            ORDER BY total_duration DESC
            LIMIT 100;`;
        let data = [];
        return sqlExec(sql, data);
    },
    async checkinTrend(uid) {
        let sql = `SELECT
                DATE_FORMAT(time, '%Y-%m-%d') as checkin_date,
                SUM(duration) AS total_duration
            FROM FocusCard
            WHERE uid = ? and time >= CURDATE() - INTERVAL 7 DAY
            GROUP BY checkin_date
            ORDER BY checkin_date`;
        let data = [uid];
        return sqlExec(sql, data);
    },
    async getAllFocuscard(uid, page) {
        let sql = `SELECT
                f.id as id,
                u.username as username,
                u.id as uid,
                DATE_FORMAT(f.time, '%Y-%m-%d %H:%i:%s') as time,
                f.duration as duration,
                f.consecutiveDays as consecutiveDays
            FROM FocusCard as f
            left join User as u on u.id = f.uid
            order by f.time desc
            limit ?, 50`;
        let data = [ (page-1) * 50 ];
        return sqlExec(sql, data);
    }
};

module.exports = bookDao;